use base_data::channel::EndReason;
use persistence::sqlx_p::DB;
use sqlx::{postgres::PgRow, Row};
use time::OffsetDateTime;
use tools::{
	global_defin::{
		config::global_config,
		global::{format_current_dt_by_default, format_date_time, DEFAULT_DATATIME_FORMAT, EMPTY_STR},
	},
	utils::current_data_time_with_zone,
	ResultEx,
};
use tracing::{debug, error, info};

use crate::{
	business::{
		calling_card::CallingCardConf, inbound400::context::InboundRunContext, NodeInfo, TurnNumInfo,
		TurnnumCallType, ISP,
	},
	config::{business_config, platform_id},
	CallingLogType,
};

///业务逻辑向持久层写入和读取的操作
#[derive(Debug)]
pub struct InboundDBO;

impl InboundDBO {
	///从数据库获取当前可使用的所有主叫号码
	pub async fn load_dial_test_callers() -> Vec<String> {
		let sql = "select caller from pt_dial_test_callers where status = 1";
		let r = sqlx::query(sql)
			.map(|r: PgRow| r.try_get("caller").unwrap_or(Default::default()))
			.fetch_all(&DB.pool)
			.await;

		match r {
			Ok(r) => r,
			Err(e) => {
				error!("得到拨测主叫号码出现错误: {}", e);

				vec![]
			}
		}
	}

	///判断呼入的号码是否属于拨测号码
	pub async fn is_dial_test_caller(caller: &str) -> ResultEx<bool> {
		let sql =
			"select count(*)::int as count from pt_dial_test_callers where caller = $1 and status = 1";
		let r = sqlx::query(sql)
			.bind(caller)
			.map(|r: PgRow| r.try_get("count").unwrap_or(0) > 0)
			.fetch_one(&DB.pool)
			.await?;

		Ok(r)
	}

	pub async fn get_calling_fnum_name(context: &InboundRunContext) -> ResultEx<String> {
		let sql = "select customer_name from t_calling_card_customername where fnum = $1 limit 1";
		let r = sqlx::query(sql)
			.bind(&context.external_num)
			.map(|r: PgRow| r.try_get("customer_name").unwrap_or("".to_owned()))
			.fetch_one(&DB.pool)
			.await?;

		Ok(r)
	}

	pub async fn get_calling_card_config(
		context: &InboundRunContext,
		called: &str,
	) -> ResultEx<Option<CallingCardConf>> {
		let sql = "select * from fn_get_calling_card_conf($1,$2);";
		let r = sqlx::query(sql)
			.bind(&context.external_num)
			.bind(called)
			.map(|r: PgRow| {
				CallingCardConf::new(
					r.try_get("key").unwrap_or("".to_owned()),
					r.try_get("url").unwrap_or("".to_owned()),
					r.try_get("mp_info_name").unwrap_or("".to_owned()),
				)
			})
			.fetch_optional(&DB.pool)
			.await?;

		Ok(r)
	}

	///记录呼叫记录
	/// 当CallingLogType 为0时代表来电时的记录，此记录多次上传时，仅记录一次，并且覆盖calling_type字段的值。
	/// 每一次呼叫被叫都应该调用此方法一次，做为被叫的呼叫记录
	/// call_flag 呼叫类型
	pub async fn record_calling_log(
		context: &InboundRunContext,
		call_log_type: CallingLogType,
		calling_type: EndReason,
		called: Option<&str>,
		node_id: Option<i32>,
		call_time: &OffsetDateTime,
		call_flag: TurnnumCallType,
	) -> ResultEx<()> {
		info!(
			"上传呼叫记录.context:{:?},call_log_type:{:?},called:{:?},node_id:{:?},calling_type:{:?}",
			context, &call_log_type, &called, &node_id, &calling_type
		);

		let called = called.unwrap_or("");
		let node_id = node_id.unwrap_or(0);

		let sql = "select * from pf_phone_400_callin_2($1,$2,$3,$4,$5,$6,$7,$8,$9,$10::timestamp without time zone,$11) ;";
		let _ = sqlx::query(sql)
			.bind(platform_id())
			.bind(context.target())
			.bind(context.caller_num())
			.bind(called)
			.bind(node_id)
			.bind(Into::<i32>::into(call_log_type))
			.bind(call_flag as i32)
			.bind(Into::<i32>::into(calling_type))
			.bind(&context.ch_call_id)
			.bind(format_date_time(call_time, &DEFAULT_DATATIME_FORMAT))
			.bind(1i32)
			.fetch_one(&DB.pool)
			.await?;

		Ok(())
	}

	///计费,并记录通话记录
	pub async fn record_and_charge(
		context: &InboundRunContext,
		called: &str,
		rec_file_path: &Option<String>,
		talk_type: EndReason,
		node_id: i32,
		dtmf: &str,
		surver_dtmf: &str,
	) -> ResultEx<()> {
		info!("上传通话记费.");

		let rec_path = rec_file_path.as_ref().unwrap_or(&EMPTY_STR);
		let sql = "select * from pf_phone_400_charge_2($1,$2,$3::timestamp without time zone,$4::timestamp without time zone,$5,$6,$7,$8,$9,$10,$11,$12,$13);";
		let _ = sqlx::query(sql)
			.bind(platform_id())
			.bind(&context.target())
			.bind(format_date_time(
				&context.charge_time.unwrap_or(context.now_call_time),
				&DEFAULT_DATATIME_FORMAT,
			))
			.bind(format_current_dt_by_default())
			.bind(&context.caller_num())
			.bind(called)
			.bind(rec_path)
			.bind(Into::<i32>::into(talk_type))
			.bind(node_id)
			.bind(dtmf)
			.bind(surver_dtmf)
			.bind(1i32)
			.bind(&context.ch_call_id)
			.fetch_one(&DB.pool)
			.await?;

		Ok(())
	}

	///呼叫结束的上传通知
	pub async fn record_call_over(context: &InboundRunContext, flag: EndReason) -> ResultEx<()> {
		info!("上传呼叫整体结束.");
		let talk_start_time = if let Some(tt) = context.talk_start_time {
			format_date_time(&tt, &DEFAULT_DATATIME_FORMAT)
		} else {
			format_date_time(
				&context.charge_time.unwrap_or(current_data_time_with_zone()),
				&DEFAULT_DATATIME_FORMAT,
			)
		};

		let sql = "select * from pf_phone_400_call_over_2($1,$2,$3::timestamp without time zone,$4::timestamp without time zone,$5,$6,1);";
		let _ = sqlx::query(sql)
			.bind(&context.ch_call_id)
			.bind(context.target())
			.bind(talk_start_time) //开始通话时间。这个是时间需要记录
			.bind(format_current_dt_by_default())
			.bind(Into::<i32>::into(flag))
			.bind(platform_id())
			.fetch_one(&DB.pool)
			.await?;
		Ok(())
	}

	///根据呼入的信息,从数据库当中获取目的码相关内容
	pub(crate) async fn load_target_info_set_context(
		context: &mut InboundRunContext,
	) -> ResultEx<()> {
		let sql = format!(
			"select pflag as flag, pflag1 as flag1, plocaltalkrate::real as localtalkrate, 
		plongtalkrate::real as longtalkrate, pmoney::real  as money, pmaxlimitcalltime as maxlimitcalltime,
		proxyid as proxyid, pphonecount as phonecount, pfnum as fnum 
		from pf_phone_400_get_info('{}')",
			context.target()
		);

		sqlx::query(&sql)
			.map(|r: PgRow| {
				context.flag = r.try_get("flag").unwrap_or(0);
				context.talk_rate = r.try_get("longtalkrate").unwrap_or(0_f32);
				context.money = r.try_get("money").unwrap_or(0_f32);
				context.max_limit_call_time = r.try_get("maxlimitcalltime").unwrap_or(0);
				context.phone_count = r.try_get("phonecount").unwrap_or(0);
				context.external_num = r.try_get("fnum").unwrap_or(String::default());
			})
			.fetch_one(&DB.pool)
			.await?;

		//数据不全,没有必要再走后面的逻辑了.直接返回.
		if context.caller_num().is_empty()
			|| context.target().is_empty()
			|| context.external_num.is_empty()
		{
			return Ok(())
		};

		//拿主叫区号
		let sql = "select * from pf_phone_area_2($1);";
		context.caller_area = sqlx::query(sql)
			.bind(context.caller_num())
			.map(|r: PgRow| {
				(
					r.try_get("code").unwrap_or("0000".to_owned()),
					r.try_get("name").unwrap_or(String::default()),
				)
			})
			.fetch_one(&DB.pool)
			.await?;

		info!("收到呼叫。获得目的码信息：{:?}", &context);

		context.node_info = InboundDBO::get_node_info(0, context).await?;

		Ok(())
	}

	///从持久层拿节点的相关信息
	pub async fn get_node_info(node_id: i32, context: &mut InboundRunContext) -> ResultEx<NodeInfo> {
		//目前这里只拿单个节点呼叫的，后续从这里扩展开
		//拿第一层节点的数据
		let sql = "SELECT  parentid,underkeylen, dtfm, connbeginbellpath, flag, nodeid, immconnect 
		FROM pf_phone_400_get_nodes_info($1,$2,$3);";

		let node = sqlx::query(sql)
			.bind(context.target())
			.bind(node_id)
			.bind(&context.caller_area.0)
			.map(|r: PgRow| NodeInfo {
				parent_id: r.try_get("parentid").unwrap_or(0),
				under_key_len: r.try_get("underkeylen").unwrap_or(0),
				dtfm: r.try_get("dtfm").unwrap_or_default(),
				conn_begin_bell_path: r
					.try_get("connbeginbellpath")
					.unwrap_or(business_config().default_wait_bell_path.to_owned()),
				flag: r.try_get("flag").unwrap_or(0),
				node_id: r.try_get("nodeid").unwrap_or(0),
				immconnect: r.try_get("immconnect").unwrap_or(0) != 0,
				node_type: r.try_get::<i32, &str>("flag").into(),
				turn_nums: Vec::new(),
			})
			.fetch_one(&DB.pool)
			.await?;

		info!("获取的本层节点信息：{:?}", &node);
		Ok(node)
	}

	///从持久层拿下级节点信息
	pub async fn get_sub_node_info(
		node_id: i32,
		context: &mut InboundRunContext,
	) -> ResultEx<Vec<NodeInfo>> {
		//目前这里只拿单个节点呼叫的，后续从这里扩展开
		//拿第一层节点的数据
		let sql = "SELECT parentid,underkeylen, dtfm, connbeginbellpath, flag, nodeid, immconnect 
			FROM pf_phone_400_get_nodes_info($1,$2,$3);";

		let node = sqlx::query(sql)
			.bind(context.target())
			.bind(node_id)
			.bind(&context.caller_area.0)
			.map(|r: PgRow| NodeInfo {
				parent_id: r.try_get("parentid").unwrap_or(0),
				under_key_len: r.try_get("underkeylen").unwrap_or(0),
				dtfm: r.try_get("dtfm").unwrap_or_default(),
				conn_begin_bell_path: r
					.try_get("connbeginbellpath")
					.unwrap_or(business_config().default_wait_bell_path.to_owned()),
				flag: r.try_get("flag").unwrap_or(0),
				node_id: r.try_get("nodeid").unwrap_or(0),
				immconnect: r.try_get("immconnect").unwrap_or(0) != 0,
				node_type: r.try_get::<i32, &str>("flag").into(),
				turn_nums: Vec::new(),
			})
			.fetch_all(&DB.pool)
			.await?;

		info!("获取的下级节点信息：{:?}", &node);
		Ok(node)
	}

	///从号码节点拿号码的相关内容
	pub async fn get_node_turnnum_info(
		node_id: i32,
		target: &str,
		caller: &str,
		use_ex_gw: bool,
	) -> ResultEx<Vec<TurnNumInfo>> {
		debug!(
			"开始获取节点的转接号码信息.node_id:{},target:{},caller:{}",
			node_id, target, caller
		);
		let sql = "select turnnum, waitminute, flag, area as jobnum, nodedtlid,is_sip
			FROM pf_phone_400_get_node_nums_by_sip($1,$2,$3);";

		let turn_nums = sqlx::query(sql)
			.bind(target)
			.bind(caller)
			.bind(node_id)
			.map(|r: PgRow| {
				TurnNumInfo::new(
					r.try_get("turnnum").unwrap_or_default(),
					r.try_get("waitminute").unwrap_or(60),
					node_id,
					if use_ex_gw {
						TurnnumCallType::CallExGW
					} else if r.try_get("is_sip").unwrap_or(0) != 0 {
						TurnnumCallType::CallTerminal
					} else {
						TurnnumCallType::CallWithIsp
					},
				)
				// TurnNumInfo {
				// num: r.try_get("turnnum").unwrap_or_default(),
				// wait_seconds: r.try_get("waitminute").unwrap_or(60),
				// _flag: r.try_get("flag").unwrap_or(0),
				// _job_num: r.try_get("jobnum").unwrap_or_default(),
				// _id: r.try_get("nodedtlid").unwrap_or(-1),
				// is_sip_user: r.try_get("is_sip").unwrap_or(0) != 0,
				// node_id,
			})
			.fetch_all(&DB.pool)
			.await?;

		info!("获得号码信息:{:?}", turn_nums);
		Ok(turn_nums)
	}

	///上传录音文件的操作
	pub async fn update_record_file(target: &str, file_name: &str) -> ResultEx<()> {
		info!("上传通话录音");

		let sql = "select *	FROM pf_platform_add_filetask(0,$1,$2,$3,$4,$5,1);";

		let _ = sqlx::query(sql)
			.bind(target)
			.bind(platform_id())
			.bind(file_name)
			.bind(global_config().rec_path)
			.bind(global_config().ftp_path)
			.fetch_one(&DB.pool)
			.await?;

		Ok(())
	}

	///返回此主叫是否有权拨打此号码.
	/// true:允许拨打
	pub async fn allow_callin(caller: &str, target: &str) -> ResultEx<i32> {
		let sql = "select pf_phone_allow_callin($1,$2) as r;";

		let r = sqlx::query(sql)
			.bind(target)
			.bind(caller)
			.map(|r: PgRow| r.try_get("r").unwrap_or(1))
			.fetch_one(&DB.pool)
			.await?;

		Ok(r)
	}

	///返回留言彩铃路径
	///指定可转接号码全为空
	pub async fn get_leave_msg_path(target: &str, node_id: i32, is_empty: bool) -> ResultEx<String> {
		let sql = "select 1 as r FROM pf_phone_400_node_leave_messae_voice($1,$2,$3);";
		let p = if is_empty { 1 } else { 0 };

		let r = sqlx::query(sql)
			.bind(target)
			.bind(node_id)
			.bind(p)
			.map(|r: PgRow| {
				r.try_get("r")
					.unwrap_or(business_config().leave_message_bell_path.to_owned())
			})
			.fetch_one(&DB.pool)
			.await?;

		Ok(r)
	}

	///得到转至sip中继的相关信息
	pub async fn get_sip_transfer_info(target: &str) -> ResultEx<(String, String)> {
		let sql = "select url,phone_num FROM pt_sip_profile where target = $1";

		let r = sqlx::query(sql)
			.bind(target)
			.map(|r: PgRow| {
				(
					r.try_get("url").unwrap_or("".to_owned()),
					r.try_get("phone_num").unwrap_or("".to_owned()),
				)
			})
			.fetch_one(&DB.pool)
			.await?;

		Ok(r)
	}

	///判断某一个目的码的主节点下是否有绑定电话，不管其他限制
	/// 返回true 为存在绑定电话
	pub async fn node_has_phone(target: &str) -> ResultEx<bool> {
		let sql = "select pf_phone_get_numnode_bind_phone_flag($1) ";

		let r = sqlx::query(sql)
			.bind(target)
			.map(|r: PgRow| r.try_get(0).unwrap_or(1))
			.fetch_one(&DB.pool)
			.await?;

		Ok(r == 1)
	}

	///获取号码区号和运营商相关信息
	pub async fn get_num_isp_info(number: &str) -> ResultEx<Option<(ISP, String)>> {
		let sql = "select isp,area_id from pt_numcollate where phone_section = $1 ";
		let r = sqlx::query(sql)
			.bind(&number[..7])
			.map(|r: PgRow| {
				(
					r.try_get("isp").unwrap_or("other").into(),
					r.try_get("area_id").unwrap_or("".to_owned()),
				)
			})
			.fetch_optional(&DB.pool)
			.await?;

		Ok(r)
	}

	///查找需要拨测的信息
	/// 返回数据，如果没有则为None
	/// 有，第一个是主叫，第二个是被叫
	pub async fn get_dial_test_info() -> ResultEx<Option<String>> {
		let sql = "select called_num from pt_dial_test where status = 0 order by id limit 1;";
		let r = sqlx::query(sql)
			.map(|r: PgRow| r.try_get("called_num").unwrap_or(String::default()))
			.fetch_optional(&DB.pool)
			.await?;

		Ok(r)
	}

	///上传一次拨测的完成。这个仅针对拨测的主叫部分，上传的是主叫的拨号完成操作
	pub async fn dial_test_call_over(caller: &str, called: &str, status: i32) -> ResultEx<()> {
		debug!(
			"开始上传拨测完成信息. caller:{},called:{},status:{}",
			caller, called, status
		);
		let sql = "select * from pf_report_dial_test_caller($1,$2,$3,$4)";
		let _ = sqlx::query(sql)
			.bind(platform_id())
			.bind(caller)
			.bind(called)
			.bind(status)
			.fetch_one(&DB.pool)
			.await?;

		Ok(())
	}

	///上传一次拨测的完成。这个仅针对拨测的被叫部分，
	/// 上传的是被叫收到的信息
	pub async fn callin_dial_test_up(caller: &str, called: &str, status: EndReason) -> ResultEx<()> {
		let sql = "select * from pf_report_dial_test($1,$2,$3,$4)";
		let _ = sqlx::query(sql)
			.bind(platform_id())
			.bind(caller)
			.bind(called)
			.bind(i32::from(status))
			.fetch_one(&DB.pool)
			.await?;

		Ok(())
	}

	///向后台发送来电名片的开始操作
	pub async fn calling_card_start(
		context: &InboundRunContext,
		called: &str,
		billing_id: String,
		rcode: String,
		mp_name: &str,
	) -> ResultEx<()> {
		let sql =
			"select * from fn_calling_card($1,$2,$3,$4,$5,$6,$7::timestamp without time zone,$8);";
		let _ = sqlx::query(sql)
			.bind(platform_id())
			.bind(context.target())
			.bind(called)
			.bind(&context.external_num)
			.bind(billing_id)
			.bind(rcode)
			.bind(format_date_time(
				&current_data_time_with_zone(),
				&DEFAULT_DATATIME_FORMAT,
			))
			.bind(mp_name)
			.fetch_one(&DB.pool)
			.await?;

		Ok(())
	}
}
